package com.define.ssh.db;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 顶一个泛型的工具类
 * 
 * @author hcy
 * 
 * @param <T>
 */
public class DBHelp<T> {
	// 定义几个常量
	/*
	 * private static final String DRIVER = "com.mysql.jdbc.Driver"; private
	 * static final String URL = "jdbc:mysql://192.168.1.64:3306/trace"; private
	 * static final String DB_NAME = "root"; private static final String
	 * DB_PASSWORD = "123456";
	 */

	/*
	 * private static BasicDataSource ds = new BasicDataSource(); static {
	 * Properties properties = new Properties(); try {
	 * properties.load(DBHelp.class
	 * .getClassLoader().getResourceAsStream("database.properties")); } catch
	 * (IOException e) { e.printStackTrace(); } String driver =
	 * properties.getProperty("jdbc.driver"); String url =
	 * properties.getProperty("jdbc.url"); String username =
	 * properties.getProperty("jdbc.username"); String password =
	 * properties.getProperty("jdbc.password"); ds.setDriverClassName(driver);
	 * ds.setUrl(url); ds.setUsername(username); ds.setPassword(password);
	 * ds.setInitialSize(5); ds.setMaxWait(5000); ds.setMaxActive(20);
	 * ds.setMinIdle(10); System.out.println("数据库初始化已完成....."); }
	 */

	/***
	 * 获取数据库连接对象
	 * 
	 * @return
	 */
	public Connection getConnection() throws Exception {
		try {
			final Properties properties = new Properties();
			try {
				properties.load(DBHelp.class.getClassLoader()
						.getResourceAsStream("database.properties"));
			} catch (final IOException e) {
				e.printStackTrace();
			}
			final String driver = properties.getProperty("jdbc.driver");
			final String url = properties.getProperty("jdbc.url");
			final String username = properties.getProperty("jdbc.username");
			final String password = properties.getProperty("jdbc.password");

			// Connection conn = ds.getConnection();
			Class.forName(driver);
			final Connection conn = DriverManager.getConnection(url, username,
					password);
			return conn;
		} catch (final Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/***
	 * 执行insert update delete语句
	 * 
	 * @param sql
	 * @param args
	 */
	public int executeSQL(String sql, Object... args) throws Exception {
		Connection conn = null;
		PreparedStatement stat = null;
		int rows = 0;
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i + 1, args[i]);
			}
			rows = stat.executeUpdate();
			/*
			 * if(rows > 0) { System.out.println("ok"); } else {
			 * System.out.println("no"); } System.out.println("SQL:" + sql);
			 */
		} catch (final SQLException e) {
			throw e;
		} finally {
			close(stat, conn);
		}
		return rows;
	}

	/**
	 * 返回一个对象的查询
	 * 
	 * @param sql
	 *            SQL语句
	 * @param mapper
	 *            该接口的所有实现类对象
	 * @param args
	 *            不定项参数
	 * @return
	 */
	public T executeQueryForObject(String sql, RowMapper<T> mapper,
			Object... args) throws Exception {
		final Connection conn = getConnection();
		PreparedStatement stat = null;
		ResultSet rs = null;
		T t = null;
		try {
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i + 1, args[i]);
			}
			rs = stat.executeQuery();
			if (rs.next()) {
				t = mapper.mapperRow(rs);
			}
			// System.out.println("SQL:" + sql);
		} catch (final SQLException e) {
			throw e;
		} finally {
			close(rs, stat, conn);
		}
		return t;
	}

	/**
	 * 根据聚合函数进行查询
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	public int executeQueryForCount(String sql, Object... args)
			throws Exception {
		final Connection conn = getConnection();
		PreparedStatement stat = null;
		ResultSet rs = null;
		int t = 0;
		try {
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i + 1, args[i]);
			}
			rs = stat.executeQuery();
			if (rs.next()) {
				t = rs.getInt(1);
			}
			// System.out.println("SQL:" + sql);
		} catch (final SQLException e) {
			throw e;
		} finally {
			close(rs, stat, conn);
		}
		return t;
	}

	/**
	 * 返回一个集合的查询
	 * 
	 * @param sql
	 *            → SQL语句
	 * @param rm
	 *            → RowMapper<T>接口的实现类对象
	 * @param args
	 *            → 不定项参数
	 * @return
	 * */
	public List<T> executeQueryForList(String sql, RowMapper<T> mapper,
			Object... args) throws Exception {
		final Connection conn = getConnection();
		PreparedStatement stat = null;
		ResultSet rs = null;
		final List<T> list = new ArrayList<T>();
		try {
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i + 1, args[i]);
			}
			rs = stat.executeQuery();
			while (rs.next()) {
				final T t = mapper.mapperRow(rs);
				list.add(t);
			}
			// System.out.println("SQL:" + sql);
		} catch (final SQLException e) {
			throw e;
		} finally {
			close(rs, stat, conn);
		}
		return list;
	}

	/**
	 * 释放连接
	 * 
	 * @param stat
	 * @param conn
	 */
	public void close(PreparedStatement stat, Connection conn) {
		close(null, stat, conn);
	}

	/**
	 * 释放连接
	 * 
	 * @param rs
	 * @param stat
	 * @param conn
	 */
	public void close(ResultSet rs, PreparedStatement stat, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (final SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (stat != null) {
					stat.close();
				}
			} catch (final SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (conn != null) {
						conn.close();
					}
				} catch (final SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}